package user;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import regist.User;


public class Userjdbc {
	
	public static PreparedStatement getCon(String sql) throws Exception{
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/research?characterEncoding=utf-8";
		Connection con = DriverManager.getConnection(url, "root", "123456");
		PreparedStatement pre = con.prepareStatement(sql);
		//con.close();
		return pre;
	}
	
	
	
	public ArrayList<User> queryUser() throws Exception {
		String sql="select * from user";
		PreparedStatement pre=Userjdbc.getCon(sql);
		ResultSet res=pre.executeQuery();
		ArrayList<User> list=new ArrayList<User>();
		while(res.next()){
			String id=res.getString(1);
			String name=res.getString(2);
			String psw=res.getString(3);
			String sex=res.getString(4);
			String age=res.getString(5);
			User qu=new User(id,name,psw,sex,age);
			list.add(qu);
		}
		return list;
	}
	
	
	public void saveUser(String id,String name,String psw,String sex,String age) throws Exception{
		String sql="insert into user values(?,?,?,?,?)";
		PreparedStatement pre=Userjdbc.getCon(sql);
		pre.setString(1, id);
		pre.setString(2, name);
		pre.setString(3, psw);
		pre.setString(4, sex);
		pre.setString(5, age);
		pre.execute();
	}
	
	public void updateUser(String id,String name,String psw,String sex,String age) throws Exception{
		String sql="update user set username=?,password=?,sex=?,age=? where userid=?";
		PreparedStatement pre=Userjdbc.getCon(sql);
		pre.setString(1,name);
		pre.setString(2,psw);
		pre.setString(3,sex);
		pre.setString(4,age);
		pre.setString(5,id);
		pre.execute();
	}
	
	
	public ArrayList<User> UserById(String id) throws Exception {
		String sql="select * from user where userid=?";
		PreparedStatement pre=Userjdbc.getCon(sql);
		pre.setString(1, id);
		ResultSet res=pre.executeQuery();
		ArrayList<User> list=new ArrayList<User>();
		while(res.next()){
			String id1=res.getString(1);
			String name=res.getString(2);
			String psw=res.getString(3);
			String age=res.getString(4);
			String sex=res.getString(5);
			User qu=new User(id1,name,psw,age,sex);
			list.add(qu);
		}
		return list;
	}
	
	public void deleteUser(String id) throws Exception {
		// TODO Auto-generated method stub
		String sql="delete from user where userid=?";
		Class.forName(TestPro.readProper("name"));
		String url=TestPro.readProper("url");
		java.sql.Connection con = DriverManager.getConnection(url, TestPro.readProper("username"), TestPro.readProper("psw"));
		PreparedStatement pre=con.prepareStatement(sql);
		pre.setString(1,id);
		pre.execute();
		con.close();
	}
	
	
}
